package com.hh.base.util.excel;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by liuhanhui on 2017/10/27.
 */
public class ExcelUtil {

    public static Map<String, List<TableRow>> getAllSheets(String path) throws IOException {
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        Iterator<Sheet> sheetIterator = xssfWorkbook.sheetIterator();
//        List<Sheet> sheets = new ArrayList<Sheet>();
        Map<String, List<TableRow>> tableMap = new HashMap<String, List<TableRow>>();
        while (sheetIterator.hasNext()) {
            Sheet sheet = sheetIterator.next();
            List<TableRow> rowList = new ArrayList<TableRow>();
            Iterator<Row> rowIterator = sheet.rowIterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                TableRow tableRow = new TableRow();
                for (int i = row.getFirstCellNum(); i < row.getLastCellNum() + 1; i++) {
                    Cell cell = row.getCell(i);
                    tableRow.addValue(getCellValue(cell));
                }
                rowList.add(tableRow);
            }
            tableMap.put(sheet.getSheetName(), rowList);
        }
        return tableMap;
    }

    private static String getCellValue(Cell cell) {
        String cellValue;
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellTypeEnum()) {
                // 如果当前Cell的Type为NUMERIC
                case NUMERIC: {
                    short format = cell.getCellStyle().getDataFormat();
                    if (format == 14 || format == 31 || format == 57 || format == 58) {   //excel中的时间格式
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil.getJavaDate(value);
                        cellValue = sdf.format(date);
                    }
                    // 判断当前的cell是否为Date
                    else if (HSSFDateUtil.isCellDateFormatted(cell)) {  //先注释日期类型的转换，在实际测试中发现HSSFDateUtil.isCellDateFormatted(cell)只识别2014/02/02这种格式。
                        // 如果是Date类型则，取得该Cell的Date值           // 对2014-02-02格式识别不出是日期格式
                        Date date = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                        cellValue = formater.format(date);
                    } else { // 如果是纯数字
                        // 取得当前Cell的数值
                        cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());

                    }
                    break;
                }
                // 如果当前Cell的Type为STRIN
                case STRING:
                    // 取得当前的Cell字符串
                    cellValue = cell.getStringCellValue().replaceAll("'", "''");
                    break;
                case BLANK:
                    cellValue = null;
                    break;
                // 默认的Cell值
                default: {
                    cellValue = " ";
                }
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }
}
